from db.models.scene import Scene
from db.repository.base import BaseRepository
from sqlalchemy.orm import Session
from sqlalchemy import or_, and_, desc
from typing import Optional, List, Dict, Any
import json


class SceneRepository(BaseRepository[Scene]):
    def __init__(self, db: Session):
        super().__init__(db, Scene)

    def get_by_scene(self, scene: str) -> Optional[Scene]:
        """根据场景名称获取场景"""
        return self.get_by(scene=scene)

    def get_by_scene_and_platform(self, scene: str, platform: str) -> Optional[Scene]:
        """根据场景名称和平台位置获取场景"""
        return (
            self.db.query(Scene)
            .filter(Scene.scene == scene, Scene.platform == platform)
            .first()
        )

    def get_by_platform(self, platform: str) -> List[Scene]:
        """根据平台位置获取场景列表"""
        return self.get_all(platform=platform)

    def get_scenes_by_item(self, item: str) -> List[Scene]:
        """根据物品名称获取包含该物品的场景"""
        all_scenes = self.get_all()
        result = []
        for scene in all_scenes:
            if item in scene.items:
                result.append(scene)
        return result

    def create_scene(self, scene: str, platform: str, items: List[str]) -> Scene:
        """创建新场景"""
        scene_data = {"scene": scene, "platform": platform, "items": items}
        return self.create(scene_data)

    def update_scene(
        self,
        scene_id: str,
        scene: str = None,
        platform: str = None,
        items: List[str] = None,
    ) -> Optional[Scene]:
        """更新场景信息"""
        obj = self.get(scene_id)
        if obj:
            update_data = {}
            if scene is not None:
                update_data["scene"] = scene
            if platform is not None:
                update_data["platform"] = platform
            if items is not None:
                update_data["items"] = items
            return self.update(obj, update_data)
        return None

    def update_scene_by_name(
        self, scene_name: str, platform: str = None, items: List[str] = None
    ) -> Optional[Scene]:
        """根据场景名称更新场景信息"""
        obj = self.get_by_scene(scene_name)
        if obj:
            update_data = {}
            if platform is not None:
                update_data["platform"] = platform
            if items is not None:
                update_data["items"] = items
            return self.update(obj, update_data)
        return None

    def delete_scene(self, scene_id: str) -> bool:
        """删除场景"""
        obj = self.get(scene_id)
        if obj:
            self.delete(obj)
            return True
        return False

    def delete_scene_by_name(self, scene_name: str) -> bool:
        """根据场景名称删除场景"""
        obj = self.get_by_scene(scene_name)
        if obj:
            self.delete(obj)
            return True
        return False

    def get_all_scenes(self) -> List[Scene]:
        """获取所有场景"""
        return self.get_all()

    def get_scenes_by_platform_list(self, platforms: List[str]) -> List[Scene]:
        """根据平台列表获取场景"""
        return self.db.query(Scene).filter(Scene.platform.in_(platforms)).all()

    def search_scenes_by_name(self, scene_name: str) -> List[Scene]:
        """根据场景名称搜索场景（模糊搜索）"""
        return self.db.query(Scene).filter(Scene.scene.ilike(f"%{scene_name}%")).all()

    def get_unique_platforms(self) -> List[str]:
        """获取所有唯一的平台位置"""
        result = self.db.query(Scene.platform).distinct().all()
        return [row[0] for row in result]

    def get_unique_items(self) -> List[str]:
        """获取所有唯一的物品"""
        all_scenes = self.get_all()
        unique_items = set()
        for scene in all_scenes:
            if scene.items:
                unique_items.update(scene.items)
        return sorted(list(unique_items))

    def get_scene_statistics(self) -> Dict[str, Any]:
        """获取场景统计信息"""
        total_scenes = self.db.query(Scene).count()

        # 平台分布
        platform_dist = {}
        platform_results = (
            self.db.query(Scene.platform, self.db.func.count(Scene.id))
            .group_by(Scene.platform)
            .all()
        )
        for platform, count in platform_results:
            platform_dist[platform] = count

        # 获取所有物品统计
        all_items = {}
        all_scenes = self.get_all()
        for scene in all_scenes:
            if scene.items:
                for item in scene.items:
                    all_items[item] = all_items.get(item, 0) + 1

        # 最常见的物品
        most_common_items = sorted(all_items.items(), key=lambda x: x[1], reverse=True)[
            :10
        ]

        return {
            "total_scenes": total_scenes,
            "platform_distribution": platform_dist,
            "most_common_items": most_common_items,
            "unique_items_count": len(all_items),
        }

    def bulk_create_scenes(self, scenes_data: List[Dict[str, Any]]) -> List[Scene]:
        """批量创建场景"""
        created_scenes = []
        for scene_data in scenes_data:
            scene = self.create(scene_data)
            created_scenes.append(scene)
        return created_scenes

    def get_scenes_with_items_count(
        self, min_items: int = 0, max_items: int = None
    ) -> List[Dict[str, Any]]:
        """获取指定物品数量范围的场景"""
        query = self.db.query(Scene)

        # 在Python中过滤物品数量，因为items是JSON字段
        result = []
        for scene in query.all():
            items_count = len(scene.items) if scene.items else 0
            if items_count >= min_items:
                if max_items is None or items_count <= max_items:
                    result.append(
                        {
                            "id": str(scene.id),
                            "scene": scene.scene,
                            "platform": scene.platform,
                            "items": scene.items,
                            "items_count": items_count,
                            "created_at": scene.created_at.isoformat()
                            if scene.created_at
                            else None,
                            "updated_at": scene.updated_at.isoformat()
                            if scene.updated_at
                            else None,
                        }
                    )

        return result
